package com.hjy.cabinet.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.hjy.cabinet.db.domain.CabinetInfo;

import java.util.ArrayList;
import java.util.List;

public class CabinetInfoHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "cabinet.db";
    private static final int DATABASE_VERSION = 30;

    private static final String TABLE_CABINET = "CabinetInfo";
    private static final String COLUMN_ID = "id"; // 自增主键
    private static final String COLUMN_DOOR = "door";
    private static final String COLUMN_LOCK_STATUS = "lockStatus";
    private static final String COLUMN_COMMAND_NUM = "commandNum";

    public CabinetInfoHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CABINET_TABLE = "CREATE TABLE " + TABLE_CABINET + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," // 自增主键
                + COLUMN_DOOR  + " TEXT UNIQUE, "
                + COLUMN_LOCK_STATUS + " INTEGER,"
                + COLUMN_COMMAND_NUM + " TEXT" + ")";
        db.execSQL(CREATE_CABINET_TABLE);
        insertInitialData(db);
    }

    // 插入12条初始数据
    private void insertInitialData(SQLiteDatabase db) {
        for (int i = 1; i <= 12; i++) {
            ContentValues values = new ContentValues();
            values.put(COLUMN_DOOR, String.valueOf(i));
            values.put(COLUMN_LOCK_STATUS, 0); // LockStatus 为 false
//            values.put(COLUMN_COMMAND_NUM, "CMD" + i); // 假设命令号为 "CMD1", "CMD2", ..., "CMD12"
            db.insert(TABLE_CABINET, null, values);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CABINET);
        onCreate(db);
    }

    // 插入数据
    public long addCabinet(CabinetInfo cabinet) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        // 不需要手动设置 id，数据库会自动生成
        values.put(COLUMN_DOOR, cabinet.getDoor());
        values.put(COLUMN_LOCK_STATUS, cabinet.getLockStatus() ? 1 : 0);
        values.put(COLUMN_COMMAND_NUM, cabinet.getCommandNum());
        // 插入数据并获取新生成的 id
        long id = db.insert(TABLE_CABINET, null, values);
        cabinet.setId((int) id); // 将生成的 id 设置到 cabinet 对象中
        db.close();
        return id;
    }

    // 获取单个柜子信息
    public CabinetInfo getCabinet(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_CABINET, new String[]{COLUMN_ID, COLUMN_DOOR, COLUMN_LOCK_STATUS, COLUMN_COMMAND_NUM},
                COLUMN_ID + "=?", new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            if (cursor.moveToFirst()) {
                CabinetInfo cabinet = new CabinetInfo();
                cabinet.setId(cursor.getInt(0));
                cabinet.setDoor(cursor.getString(1));
                cabinet.setLockStatus(cursor.getInt(2) == 1);
                cabinet.setCommandNum(cursor.getString(3));
                cursor.close();
                return cabinet;
            } else {
                cursor.close();
                return null;
            }
        }
        return null;
    }

    // 获取所有柜子信息
    public List<CabinetInfo> getAllCabinets() {
        List<CabinetInfo> cabinetList = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_CABINET;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                CabinetInfo cabinet = new CabinetInfo();
                cabinet.setId(cursor.getInt(0));
                cabinet.setDoor(cursor.getString(1));
                cabinet.setLockStatus(cursor.getInt(2) == 1);
                cabinet.setCommandNum(cursor.getString(3));
                cabinetList.add(cabinet);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return cabinetList;
    }

    // 更新柜子信息
    public int updateCabinet(CabinetInfo cabinet) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_DOOR, cabinet.getDoor());
        values.put(COLUMN_LOCK_STATUS, cabinet.getLockStatus() ? 1 : 0);
        values.put(COLUMN_COMMAND_NUM, cabinet.getCommandNum());
        return db.update(TABLE_CABINET, values, COLUMN_ID + "=?", new String[]{String.valueOf(cabinet.getId())});
    }

    // 删除柜子信息
    public int deleteCabinet(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        int delete = db.delete(TABLE_CABINET, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
        db.close();
        return delete;
    }
}